﻿using System;
using System.Data;
using System.Collections.Generic;
using TCKQKL.DAL.IDAL;
using TCKQKL.Service.DataContract;
using System.Data.SqlClient;
using TCKQKL.DAL.Config;

namespace TCKQKL.DAL.SQLserver
{
    public class MarketSummary : IMarketSummary
    {
        private SqlConnection _internalConnection;
        private SqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new SqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }
        public MarketSummaryDataModel getMarketSummary()
        {
            return null;
        }
        public StocksDataModel getQuote(string symbol)
        {
            return null;
        }

        //SQL strings
        private const string GET_MARKETSUMMARY_HOSE = "SET NOCOUNT ON; SELECT TOP (1) Changed, PctChanged, Id, TranDate, Time, VnIndex, TotalTrade, TotalShares, TotalValues, UpVolume, DownVolume, NoChangeVolume, Advances, Declines, NoChange, Market FROM V_TOTALMKT ORDER BY Time DESC";

        private const string GET_MARKETSTAT_HOSE = "SELECT TOP(1) CONTROLCODE, TIME FROM MARKETSTAT ORDER BY id DESC";

        private const string GET_MARKETSUMMARY_HASTC = "SELECT  TOP (1) id, FloorCode, DateNo, TradingDate, CurrentStatus, Time, TotalTrade, TotalStock, TotalQtty, TotalValue, Advances, NoChange, Declines, PriorMarketIndex, ChangedIndex, PercentIndex, CurrentIndex, MarketIndex, SessionNo, PreviousPriorMarketIndex, AverageMarketIndex, AveragePriorMarketIndex, AverageChangedIndex,AveragePercentIndex, PutThroughTotalQtty, PutThroughTotalValue, PutThroughTotalTrade FROM dbo.STS_MARKETINFO WHERE TOTALTRADE>0 ORDER BY Time DESC";

        private const string GET_MARKETSTAT_HASTC = "";

        private const string GET_QUOTE_HOSE = "SET NOCOUNT ON; SELECT  StockSymbol, SecuritiesName, Last, Ceiling, Floor, OpenPrice, Highest, Lowest, TotalVol, (CASE PriorClosePrice WHEN '0' THEN 0 ELSE ProjectOpen - PriorClosePrice END) AS Changed, (CASE Last WHEN '0' THEN (Ceiling+Floor)/2 ELSE Last END) AS NEXTREF FROM V_SECURITIES WITH (NOLOCK) WHERE StockSymbol=@StockSymbol";

        private const string GET_QUOTE_HOSE_AVG = "select AVG(PriorClosePrice) as Price, stocksymbol FROM (select top 5 * from securities_hist where stocksymbol=@StockSymbol order by [time] desc) Lite group by Lite.stocksymbol";

        private const string GET_QUOTE_HASTC = "SET NOCOUNT ON; SELECT top(1) Code, Name, BasicPrice, CeilingPrice, FloorPrice, OpenPrice, HighestPrice, LowestPrice, (TotalTradingQtty - PutThroughTotalTradedQtty) as Volume, (AveragePrice - BasicPrice)  as Changed, AveragePrice FROM STS_STOCKSINFO WITH (NOLOCK) WHERE (StockType = 2) AND (Code=@StockSymbol) ORDER BY TIME DESC";

        private const string GET_QUOTE_HASTC_AVG = "select AVG(BasicPrice) as Price, CODE FROM (select top 5 * from STS_STOCKSINFO_HIST where CODE=@StockSymbol order by [time] desc) Lite group by Lite.CODE";

        private const string GET_QUOTE_UPCOM = "SET NOCOUNT ON; SELECT top(1) Code, Name, BasicPrice, CeilingPrice, FloorPrice, OpenPrice, HighestPrice, LowestPrice, (TotalTradingQtty - PutThroughTotalTradedQtty) as Volume, (AveragePrice - BasicPrice)  as Changed, AveragePrice FROM STSO_STOCKSINFO WITH (NOLOCK) WHERE (StockType = 2) AND (Code=@StockSymbol) ORDER BY TIME DESC";
        private const string GET_QUOTE_UPCOM_AVG = "select AVG(BasicPrice) as Price, CODE FROM (select top 5 * from STSO_STOCKSINFO_HIST where CODE=@StockSymbol order by [time] desc) Lite group by Lite.CODE";

        //parameters
        private const string PARM_SYMBOL = "@StockSymbol";

        //functions
        public MarketSummaryDataModel getMarketSummaryHose()
        {
            try
            {
                string marketStat = getMarketStatHose();
                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MARKETSUMMARY_HOSE);
                MarketSummaryDataModel market = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    market = new MarketSummaryDataModel("HOSE", Convert.ToDecimal(rdr["VNINDEX"]) / 100, Convert.ToDecimal(rdr["Changed"]) / 100, Convert.ToDecimal(rdr["TOTALTRADE"]), Convert.ToDecimal(rdr["TOTALSHARES"]), Convert.ToDecimal(rdr["TOTALVALUES"]), null, null, null, null, null, null, Convert.ToDateTime(rdr["TRANDATE"]), marketStat);
                }
                rdr.Close();
                return market;
            }
            catch
            {
                throw;
            }
        }
        public string getMarketStatHose()
        {
            try
            {
                SqlDataReader rdr2 = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MARKETSTAT_HOSE);
                if (rdr2.HasRows)
                {
                    rdr2.Read();
                    if (rdr2["CONTROLCODE"].ToString() != null)
                    {
                        switch (rdr2["CONTROLCODE"].ToString().Trim())
                        {
                            case "J":
                                if (Convert.ToInt32(rdr2["TIME"]) < 83000)
                                {
                                    rdr2.Close();
                                    return Settings.NEWDAY;
                                }
                                else
                                {
                                    rdr2.Close();
                                    return Settings.CLOSE;
                                }
                            case "P":
                                rdr2.Close();
                                return Settings.PHASE1;
                            case "O":
                                rdr2.Close();
                                return Settings.PHASE2;
                            case "A":
                                rdr2.Close();
                                return Settings.PHASE3;
                            case "C":
                                rdr2.Close();
                                return Settings.RUNOFF;
                            case "K":
                                rdr2.Close();
                                return Settings.CLOSE;
                            default:
                                rdr2.Close();
                                return Settings.CLOSE;
                        }
                    }
                    else
                    {
                        rdr2.Close();
                        return Settings.CLOSE;
                    }
                }
                rdr2.Close();
                return Settings.CLOSE;
            }
            catch
            {
                throw;
            }
        }
        public MarketSummaryDataModel getMarketSummaryHastc()
        {
            try
            {
                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MARKETSUMMARY_HASTC);
                string marketStat = Settings.CLOSE;
                MarketSummaryDataModel market = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    if (DateTime.Today.Equals(Convert.ToDateTime(rdr["TRADINGDATE"])))
                    {
                        if ((DateTime.Now.CompareTo(Convert.ToDateTime(Settings.HASTC_NEWDAY)) > 0) && (DateTime.Now.CompareTo(Convert.ToDateTime(Settings.HASTC_OPEN)) <= 0))
                            marketStat = Settings.NEWDAY;
                        else
                            if (((DateTime.Now.CompareTo(Convert.ToDateTime(Settings.HASTC_OPEN))) > 0) && (DateTime.Now.CompareTo(Convert.ToDateTime(Settings.HASTC_CLOSE)) <= 0))
                                marketStat = Settings.OPEN;
                            else
                                marketStat = Settings.CLOSE;
                    }
                    else
                        marketStat = Settings.CLOSE;

                    market = new MarketSummaryDataModel("HASTC", Convert.ToDecimal(rdr["MarketIndex"]), Convert.ToDecimal(rdr["ChangedIndex"]), Convert.ToDecimal(rdr["TOTALTRADE"]), Convert.ToDecimal(rdr["TOTALQTTY"]), Convert.ToDecimal(rdr["TOTALVALUE"]) / 1000000, null, null, null, null, null, null, Convert.ToDateTime(rdr["TRADINGDATE"]), marketStat);
                }
                rdr.Close();
                return market;
            }
            catch
            {
                throw;
            }
        }

        public StocksDataModel getQuoteHose(string symbol)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 10);
                parm1.Value = symbol;
                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_QUOTE_HOSE, parm1);
                StocksDataModel quote = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    quote = new StocksDataModel(rdr["STOCKSYMBOL"].ToString(), rdr["SECURITIESNAME"].ToString(), Convert.ToDecimal(rdr["NEXTREF"]) * 10, Convert.ToDecimal(rdr["OPENPRICE"]) * 10, Convert.ToDecimal(rdr["LOWEST"]) * 10, Convert.ToDecimal(rdr["HIGHEST"]) * 10, Convert.ToDecimal(rdr["CHANGED"]) * 10, Convert.ToDecimal(rdr["TOTALVOL"]) * 10, Convert.ToDecimal(rdr["CEILING"]) * 10, Convert.ToDecimal(rdr["FLOOR"]) * 10, (Convert.ToDecimal(rdr["CEILING"]) + Convert.ToDecimal(rdr["FLOOR"])) * 5, "HOSE");
                }
                rdr.Close();
                return quote;
            }
            catch
            {
                throw;
            }
        }

        public StocksDataModel getQuoteHoseAvg(string symbol)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 10);
                parm1.Value = symbol;
                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_QUOTE_HOSE_AVG, parm1);
                StocksDataModel quote = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    quote = new StocksDataModel(rdr["STOCKSYMBOL"].ToString(),
                            rdr["STOCKSYMBOL"].ToString(),
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10,
                            Convert.ToDecimal(rdr["Price"]) * 10, "HOSE");
                }
                rdr.Close();
                return quote;
            }
            catch
            {
                throw;
            }
        }

        public StocksDataModel getQuoteHastc(string symbol)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 10);
                parm1.Value = symbol;
                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_QUOTE_HASTC, parm1);
                StocksDataModel quote = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    quote = new StocksDataModel(rdr["CODE"].ToString(), rdr["NAME"].ToString(), Convert.ToDecimal(rdr["AVERAGEPRICE"]), Convert.ToDecimal(rdr["OPENPRICE"]), Convert.ToDecimal(rdr["LOWESTPRICE"]), Convert.ToDecimal(rdr["HIGHESTPRICE"]), Convert.ToDecimal(rdr["CHANGED"]), Convert.ToDecimal(rdr["VOLUME"]), Convert.ToDecimal(rdr["CEILINGPRICE"]), Convert.ToDecimal(rdr["FLOORPRICE"]), Convert.ToDecimal(rdr["AveragePrice"]), "HNX");
                }
                rdr.Close();
                return quote;
            }
            catch
            {
                throw;
            }
        }
        public StocksDataModel getQuoteHastcAvg(string symbol)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 10);
                parm1.Value = symbol;
                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_QUOTE_HASTC_AVG, parm1);
                StocksDataModel quote = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    quote = new StocksDataModel(rdr["CODE"].ToString(),
                            rdr["CODE"].ToString(),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]), "HASTC");
                }
                rdr.Close();
                return quote;
            }
            catch
            {
                throw;
            }
        }

        public StocksDataModel getQuoteUpcom(string symbol)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 10);
                parm1.Value = symbol;
                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_QUOTE_UPCOM, parm1);
                StocksDataModel quote = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    quote = new StocksDataModel(rdr["CODE"].ToString(), rdr["NAME"].ToString(), Convert.ToDecimal(rdr["AVERAGEPRICE"]), Convert.ToDecimal(rdr["OPENPRICE"]), Convert.ToDecimal(rdr["LOWESTPRICE"]), Convert.ToDecimal(rdr["HIGHESTPRICE"]), Convert.ToDecimal(rdr["CHANGED"]), Convert.ToDecimal(rdr["VOLUME"]), Convert.ToDecimal(rdr["CEILINGPRICE"]), Convert.ToDecimal(rdr["FLOORPRICE"]), Convert.ToDecimal(rdr["AveragePrice"]), "UPCOM");
                }
                rdr.Close();
                return quote;
            }
            catch
            {
                throw;
            }
        }
        public StocksDataModel getQuoteUpcomAvg(string symbol)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar, 10);
                parm1.Value = symbol;
                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_QUOTE_UPCOM_AVG, parm1);
                StocksDataModel quote = null;
                if (rdr.HasRows)
                {
                    rdr.Read();
                    quote = new StocksDataModel(rdr["CODE"].ToString(),
                            rdr["CODE"].ToString(),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]),
                            Convert.ToDecimal(rdr["Price"]), "HASTC");
                }
                rdr.Close();
                return quote;
            }
            catch
            {
                throw;
            }
        }


    }
}
